from tools.mysqlconntool import mysqlconn
from datetime import datetime

def add_user(data):
    mydb = mysqlconn.get_connection()
    mycursor = mydb.cursor()

    sql = """
    INSERT INTO users (name, identity_card, admission_no, gender, age, fertility_history, education_level, occupation, diagnosis, comorbidity, therapeutic_modality, methods_of_medical_cost_coverage,marital_status, phone, emergency_contact, emergency_contact_phone, address, agricultural_indicator,temperature, pulse, respiration_rate, blood_pressure)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    val = (data['name'], data['identity_card'], data['admission_no'], data['gender'], data['age'],
           data['fertility_history'], data['education_level'], data['occupation'], data['diagnosis'],
           data['comorbidity'], data['therapeutic_modality'], data['methods_of_medical_cost_coverage'],
           data['marital_status'], data['phone'], data['emergency_contact'], data['emergency_contact_phone'],
           data['address'], data['agricultural_indicator'],data.get('temperature', None), data.get('pulse', None), data.get('respiration_rate', None), data.get('blood_pressure', None))
    # 执行 SQL 语句
    mycursor.execute(sql, val)
    # 获取新创建用户的 ID
    user_id = mysqlconn.get_last_row_id(mycursor)

    # 提交更改
    mydb.commit()

    # 关闭游标和连接
    mycursor.close()
    mydb.close()

    return user_id

def get_problem_list(search_query=None):
    if search_query:
        sql = "SELECT * FROM problem WHERE user_id LIKE %s" # 包含了identity_sha
        val = (f'%{search_query}%',)
    else:
        sql = "SELECT * FROM problem"
        val = None

    ratings = mysqlconn.sql_select(sql, val)
    rating_list = []
    for rating in ratings:
        rating_dict = {
            'id': rating[0],
            'user_id': rating[1],
            'evaluate_text': rating[2],
            'evaluate_voice_address': rating[3],
            'evaluate_source': rating[4],
            'answer': rating[5],
            'evaluate_time': rating[6]

        }
        rating_list.append(rating_dict)
    return rating_list
def get_all_users(search_query=None):
    if search_query:
        sql = "SELECT * FROM users WHERE identity_card LIKE %s" # 包含了identity_sha
        val = (f'%{search_query}%',)
    else:
        sql = "SELECT * FROM users"
        val = None

    users = mysqlconn.sql_select(sql, val)
    user_list = []
    for user in users:
        user_dict = {
            'id': user[0],
            'name': user[1],
            'identity_card': user[2],
            'admission_no': user[4],
            'gender': user[5],
            'age': user[6],
            'fertility_history': user[7],
            'education_level': user[8],
            'occupation': user[9],
            'diagnosis': user[10],
            'comorbidity': user[11],
            'therapeutic_modality': user[12],
            'methods_of_medical_cost_coverage': user[13],
            'marital_status': user[14],
            'phone': user[15],
            'emergency_contact': user[16],
            'emergency_contact_phone': user[17],
            'address': user[18],
            'agricultural_indicator': bool(user[19]),  # 将 tinyint(1) 转换为布尔值,
            'temperature': user[20],
            'pulse': user[21],
            'respiration_rate': user[22],
            'blood_pressure': user[23],
            'create_time': user[24],
            'update_time': user[25]
        }
        user_list.append(user_dict)
    return user_list

def get_user_ratings(identity_card):
    sql = "SELECT * FROM ratings WHERE identity_card = %s"  # 使用正确的表名
    val = (identity_card,)

    ratings = mysqlconn.sql_select(sql, val)
    rating_list = []
    for rating in ratings:
        rating_dict = {
            "id": rating[0],
            "video_name": rating[1],
            "rating": rating[2],
            "create_time": rating[4].strftime('%Y-%m-%d %H:%M:%S') if isinstance(rating[4], datetime) else None,
            "num": rating[3],
            "identity_card": rating[5]
        }
        rating_list.append(rating_dict)
    return rating_list

def insert_rating_sql(user_id, video_name, rating,train_time_sum,username,admission_no,phone):
    try:
        # 获取数据库连接
        mydb = mysqlconn.get_connection()
        mycursor = mydb.cursor()
        # 检查是否存在相同identity_card和video_name的记录
        check_sql = "SELECT * FROM ratings WHERE user_id = %s AND video_name = %s"

        check_val = (user_id, video_name)
        mycursor.execute(check_sql, check_val)
        existing_rating_all = mycursor.fetchall()
        # print("existing_rating_all", existing_rating_all)


        if existing_rating_all:
            # 如果存在记录，使num加1
            # 插入新的评价数据，如果存在相同identity_card和video_name的记录，则更新num值
            create_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            # print("existing_rating_all")
            # print(existing_rating_all)
            new_num = existing_rating_all[-1][6] + 1
            insert_sql = """
                INSERT INTO ratings (user_id, video_name, rating, create_time, num,train_time_sum,name,admission_no,phone)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_val = (user_id, video_name, rating, create_time, new_num,train_time_sum,username,admission_no,phone)
            print(insert_sql, insert_val)
            mycursor.execute(insert_sql, insert_val)
            # 提交更改
            mydb.commit()

            # 关闭游标和连接
            mycursor.close()
            mydb.close()
        else:
            # print("else")
            # 如果没有记录，将num设为1
            create_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            insert_sql = """
                INSERT INTO ratings (user_id, video_name, rating, create_time, num,train_time_sum,name,admission_no,phone)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_val = (user_id, video_name, rating, create_time, 1,train_time_sum,username,admission_no,phone)
            print(insert_sql,insert_val)
            mycursor.execute(insert_sql, insert_val)
            # 提交更改
            mydb.commit()
            # 关闭游标和连接
            mycursor.close()
            mydb.close()

        return {"status": "success", "message": "插入成功"}
    except Exception as e:
        return {"status": "error", "message": str(e)}
def insert_problem_sql(user_id, evaluate_text, video_name,username,admission_no):
    mydb = mysqlconn.get_connection()
    mycursor = mydb.cursor()
    evaluate_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    insert_sql = """
                   INSERT INTO problem (user_id, evaluate_text, evaluate_source, evaluate_time,name,admission_no)
                   VALUES (%s, %s, %s, %s, %s, %s)
               """
    insert_val = (user_id, evaluate_text, video_name, evaluate_time,username,admission_no)
    mycursor.execute(insert_sql, insert_val)
    # 提交更改
    mydb.commit()

    # 关闭游标和连接
    mycursor.close()
    mydb.close()
    return {"status": "success", "message": "插入成功"}
def get_user_detail(user_id):
    sql = """
    SELECT id, name, identity_card, admission_no, gender, age, fertility_history, education_level, occupation, diagnosis, comorbidity, therapeutic_modality, methods_of_medical_cost_coverage, marital_status, phone, emergency_contact, emergency_contact_phone, address, agricultural_indicator, temperature, pulse, respiration_rate, blood_pressure, create_time, update_time 
    FROM users 
    WHERE id = %s
    """
    val = (user_id,)
    result = mysqlconn.sql_select(sql, val) # 包含元组的列表

    if not result:
        return None
    user_tuple = result[0]
    user = {
        'id': user_tuple[0],
        'name': user_tuple[1],
        'identity_card': user_tuple[2],
        'admission_no': user_tuple[3],
        'gender': user_tuple[4],
        'age': user_tuple[5],
        'fertility_history': user_tuple[6],
        'education_level': user_tuple[7],
        'occupation': user_tuple[8],
        'diagnosis': user_tuple[9],
        'comorbidity': user_tuple[10],
        'therapeutic_modality': user_tuple[11],
        'methods_of_medical_cost_coverage': user_tuple[12],
        'marital_status': user_tuple[13],
        'phone': user_tuple[14],
        'emergency_contact': user_tuple[15],
        'emergency_contact_phone': user_tuple[16],
        'address': user_tuple[17],
        'agricultural_indicator': bool(user_tuple[18]),
        'temperature': user_tuple[19],
        'pulse': user_tuple[20],
        'respiration_rate': user_tuple[21],
        'blood_pressure': user_tuple[22],
        'create_time': user_tuple[23],
        'update_time': user_tuple[24]
    }

    sql = "SELECT * FROM user_psychological_test_record WHERE user_id = %s"
    psychological_records = mysqlconn.sql_select(sql, val)

    return user, psychological_records

def delete_user(user_id):
    # 删除 psychological_records 表中对应的记录
    sql_psychological = "DELETE FROM user_psychological_test_record WHERE user_id = %s"
    val_psychological = (user_id,)
    mysqlconn.sql_update(sql_psychological, val_psychological)

    # 删除 users 表中对应的记录
    sql_users = "DELETE FROM users WHERE id = %s"
    val_users = (user_id,)
    mysqlconn.sql_update(sql_users, val_users)

    return True

def update_user(user_id, data):
    sql = "SELECT * FROM users WHERE id = %s"
    val = (user_id,)
    result = mysqlconn.sql_select(sql, val)
    if not result:
        return False
    print(f'result:{result}')
    print(f'更新data:{data}')
    user = result[0]
    sql = """
    UPDATE users SET    name= %s,
                        admission_no= %s,
                        identity_card= %s,
                        diagnosis= %s,
                        comorbidity= %s,
                        age= %s,
                        therapeutic_modality= %s,
                        gender= %s,
                        fertility_history= %s,
                        inhospital_time= %s,
                        temperature= %s,
                        pulse= %s,
                        respiration_rate= %s,
                        blood_pressure= %s,
                        outhospital_time= %s,
                        temperature_out= %s,
                        pulse_out= %s,
                        respiration_rate_out= %s,
                        blood_pressure_out= %s,
                        temperature_2_months= %s,
                        pulse_2_months= %s,
                        respiration_rate_2_months= %s,
                        blood_pressure_2_months= %s,
                        marital_status= %s,
                        education_level= %s,
                        occupation= %s,
                        methods_of_medical_cost_coverage= %s,
                        address= %s,
                        phone= %s,
                        notes= %s,
                        create_time= %s,
                        update_time= %s,
                        password= %s,
                        roles= %s,
                        group_class= %s WHERE id = %s
    """
    val = (
        data.get('name', user[1]),
        data.get('admission_no', user[2]),
        data.get('identity_card', user[3]),
        data.get('diagnosis', user[4]),
        data.get('comorbidity', user[5]),
        data.get('age', user[6]),
        data.get('therapeutic_modality', user[7]),
        data.get('gender', user[8]),
        data.get('fertility_history', user[9]),
        data.get('inhospital_time', user[10]),
        data.get('temperature', user[11]),
        data.get('pulse', user[12]),
        data.get('respiration_rate', user[13]),
        data.get('blood_pressure', user[14]),
        data.get('outhospital_time', user[15]),
        data.get('temperature_out', user[16]),
        data.get('pulse_out', user[17]),
        data.get('respiration_rate_out', user[18]),
        data.get('blood_pressure_out', user[19]),
        data.get('temperature_2_months', user[20]),
        data.get('pulse_2_months', user[21]),
        data.get('respiration_rate_2_months', user[22]),
        data.get('blood_pressure_2_months', user[23]),
        data.get('marital_status', user[24]),
        data.get('education_level', user[25]),
        data.get('occupation', user[26]),
        data.get('methods_of_medical_cost_coverage', user[27]),
        data.get('address', user[28]),
        data.get('phone', user[29]),
        data.get('notes', user[30]),
        data.get('create_time', user[31]),
        data.get('update_time', user[32]),
        data.get('password', user[33]),
        data.get('roles', user[34]),
        data.get('group_class', user[35]),
        user_id
    )
    print(f'最终:{val}')
    mysqlconn.sql_update(sql, val)
    return True